package com.link.base.wechat.basic;
/**
 * @author lujiang
 */

import com.link.core.basic.query.ImportColumn;
import com.link.core.modules.imports.FileReader;
import com.link.core.modules.imports.exception.ImportsException;
import com.link.core.util.StringUtils;
import com.link.core.cllog.LCLogger;
import com.link.ext.basic.model.BasicBaseModel;
import com.link.ext.basic.model.BasicExtModel;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class StoreExcelFileReader implements FileReader {
    private File file;

    /**
     * <p>设置关联头记录的field，暂不支持关联字段为扩展字段的子表</p>
     *
     * @author yrf
     *
     * @param clazz
     *
     * @param importColumns
     *
     * @param record
     */
    protected static <T> void setParentId(Class<T> clazz, List<ImportColumn> importColumns, T record) {
        ImportColumn t = importColumns.get(0);
        if (!StringUtils.isBlank(t.getParentField())) {
            Class<?> propertyType = null;
            Field propertyField;
            try {

                String parentField = t.getParentField();
                String parentValue = t.getParentid().toString();
                propertyField = clazz.getDeclaredField(parentField);
                propertyType = propertyField.getType();
                PropertyUtils.setProperty(record, parentField, parseValueWithType(parentValue, propertyType));

            } catch (NoSuchMethodException e) {
                // TODO Auto-generated catch block
                LCLogger.withException(e);
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                LCLogger.withException(e);
            } catch (InvocationTargetException e) {
                // TODO Auto-generated catch block
                LCLogger.withException(e);
            } catch (NoSuchFieldException e) {
                // TODO Auto-generated catch block
                LCLogger.withException(e);
            } catch (SecurityException e) {
                // TODO Auto-generated catch block
                LCLogger.withException(e);
            }
        }
        // return record;
    }

    protected static <T> Object parseValueWithType(String value, Class<?> type) {
        Object result = null;
        String doubleType = "java.lang.Double";
        try { // 根据属性的类型将内容转换成对应的类型
            if (Boolean.class == type) {
                result = Boolean.parseBoolean(value);
            } else if (Byte.class == type) {
                result = Byte.parseByte(value);
            } else if (Short.class == type) {
                result = Short.parseShort(value);
            } else if (Integer.class == type) {
                result = Integer.parseInt(value);
            } else if (Long.class == type) {
                result = Long.parseLong(value);
            } else if (Float.class == type) {
                result = Float.parseFloat(value);
            } else if (Double.class == type) {
                result = Double.parseDouble(value);
            } else if (Class.forName(doubleType) == type) {
                result = Double.parseDouble(value);
            } else {
                result = (Object) value;
            }
        } catch (Exception e) {
            // 把异常吞掉直接返回null
        }
        return result;
    }

    public File getFile() {
        return file;
    }

    public void setFile(File file) {
        this.file = file;
    }

    @Override
    public String getEncoding() {

        return null;
    }

    @Override
    public void setEncoding(String encoding) {


    }

    @Override
    public List<ImportColumn> readHeader(File file, String fileext) throws ImportsException {

        List<ImportColumn> list = new ArrayList<ImportColumn>();

        try {
            this.file = file;
            String xls = ".xls";
            String xlsx = ".xlsx";
            if (xls.equals(fileext)) {
                HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
                HSSFSheet sheet = workbook.getSheetAt(0);
                //门店消费者是从第7行解析表头
                HSSFRow hssfRow = sheet.getRow(6);
                for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
                    HSSFCell cell = hssfRow.getCell(i);
                    ImportColumn importColumn = new ImportColumn();
                    importColumn.setTitle(cell.getStringCellValue());
                    list.add(importColumn);
                }
            }
            if (xlsx.equals(fileext)) {
                XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
                //第一张表
                XSSFSheet sheet = workbook.getSheetAt(0);
                //门店消费者是从第7行解析表头
                XSSFRow row = sheet.getRow(6);
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    XSSFCell cell = row.getCell(i);
                    ImportColumn importColumn = new ImportColumn();
                    importColumn.setTitle(cell.getStringCellValue());
                    list.add(importColumn);
                }
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            LCLogger.withException(e);
        }
        return list;
    }

    @Override
    public <T> List<T> readContent(Class<T> clazz, List<ImportColumn> importColumns, List<ImportColumn> headers,
                                   Map<?, ?> defaultMap, String fileext) throws ImportsException {
        List<T> result = new ArrayList<T>();
        try {
            String xls = ".xls";
            String xlsx = ".xlsx";
            if (xls.equals(fileext)) {
                result = read2003Content(clazz, importColumns, headers, defaultMap);
            }
            if (xlsx.equals(fileext)) {
                result = read2007Content(clazz, importColumns, headers, defaultMap);
            }
        } catch (Exception e) {
            // TODO: handle exception
            LCLogger.withException(e);
        }

        return result;
    }

    public <T> List<T> read2003Content(Class<T> clazz, List<ImportColumn> importColumns, List<ImportColumn> headers,
                                       Map<?, ?> defaultMap) throws ImportsException {
        List<T> result = new ArrayList<T>();
        try {

            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
            // 获取总页数
            int sheetTotal = workbook.getNumberOfSheets();
            for (int index = 0; index < sheetTotal; index++) {
                // 获取第几页
                HSSFSheet hssfsheet = workbook.getSheetAt(index);
                System.out.println(hssfsheet.getLastRowNum());
                for (int i = 1; headers != null && i <= hssfsheet.getLastRowNum(); i++) {
                    // 获取第几行数据
                    HSSFRow hssfRow = hssfsheet.getRow(i);
                    T record = clazz.newInstance();
                    // 设置关联头Id
                    setParentId(clazz, importColumns, record);
                    // 设置默认值
                    setDefaultValue(clazz, defaultMap, record);
                    for (int j = 0; j < headers.size(); j++) {
                        ImportColumn header = headers.get(j);
                        for (int k = 0; importColumns != null && k < importColumns.size(); k++) {
                            ImportColumn importColumn = importColumns.get(k);
                            if (header.getTitle().equals(importColumn.getTitle())) {
                                HSSFCell cell = hssfRow.getCell(j);
                                String content = "";
                                if (cell != null) {
                                    content = get2003CellContent(cell);
                                }
                                // 值列表显示值转换为独立源代码
                                if (!StringUtils.isBlank(importColumn.getLov())) {
                                    content = importColumn.getLovCode(content);
                                }
                                String fieldName = importColumn.getField();
                                if ("id".equalsIgnoreCase(fieldName)) {
                                    continue;
                                }
                                Class<?> propertyType = null;
                                Field propertyField;
                                try {
                                    propertyField = clazz.getDeclaredField(fieldName);
                                    propertyType = propertyField.getType();
                                } catch (NoSuchFieldException e1) {
                                    // TODO: handle exception
                                    // 匹配可扩展模块
                                    LCLogger.info().withMessageKey("read2003Content")
                                            .withMessage("Read Field '" + fieldName + "'").flush();
                                    if (record instanceof BasicBaseModel) {
                                        LCLogger.info().withMessageKey("read2003Content")
                                                .withMessage("Read Ext Field '" + fieldName + "'").flush();
                                        if (!StringUtils.isBlank(content)) {
                                            if (((BasicBaseModel) record).getExtList() == null) {
                                                ((BasicBaseModel) record).setExtList(new ArrayList<BasicExtModel>());
                                            }
                                            BasicExtModel ext = new BasicExtModel();
                                            ext.setName(fieldName);
                                            ext.setValue(content);
                                            ((BasicBaseModel) record).getExtList().add(ext);
                                            ((BasicBaseModel) record).setExtFlag(true);
                                        }
                                        break;
                                    } else {
                                        LCLogger.withException(e1);
                                    }
                                }
                                try {
                                    PropertyUtils.setProperty(record, fieldName,
                                            parseValueWithType(content, propertyType));

                                } catch (NoSuchMethodException e) {
                                    // TODO Auto-generated catch block
                                    LCLogger.withException(e);
                                } catch (IllegalAccessException e) {
                                    LCLogger.withException(e);
                                } catch (InvocationTargetException e) {
                                    // TODO Auto-generated catch block
                                    LCLogger.withException(e);
                                }
                                break;
                            }
                        }
                    }
                    result.add(record);
                }
            }

        } catch (Exception e) {
            // TODO: handle exception
            LCLogger.withException(e);
        }
        return result;
    }

    public <T> List<T> read2007Content(Class<T> clazz, List<ImportColumn> importColumns, List<ImportColumn> headers,
                                       Map<?, ?> defaultMap) throws ImportsException {
        List<T> result = new ArrayList<T>();
        try {

            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
            // 获取总页数
            int sheetTotal = workbook.getNumberOfSheets();
            for (int index = 0; index < sheetTotal; index++) {
                // 获取第几页
                XSSFSheet xssfsheet = workbook.getSheetAt(index);
                for (int i = 7; headers != null && i <= xssfsheet.getLastRowNum(); i++) {
                    //根据不同模板表设置从第几行开始读取,门店模板是从第四行开始读取
                    // 获取第几行数据
                    XSSFRow xssfRow = xssfsheet.getRow(i);
                    if (xssfRow == null) {
                        continue;
                    }
                    T record = clazz.newInstance();
                    String errorinfo = "";
                    // 设置关联头Id
                    setParentId(clazz, importColumns, record);
                    // 设置默认值
                    setDefaultValue(clazz, defaultMap, record);
                    for (int j = 0; j < headers.size(); j++) {
                        ImportColumn header = headers.get(j);
                        for (int k = 0; importColumns != null && k < importColumns.size(); k++) {
                            ImportColumn importColumn = importColumns.get(k);
                            if (header.getTitle().equals(importColumn.getTitle())) {
                                XSSFCell cell = xssfRow.getCell(j);
                                String content = "";
                                if (cell != null) {
                                    content = get2007CellContent(cell);
                                }
                                /*
                                 * if (!StringUtils.isBlank(importColumn.getLov())) { content =
                                 * importColumn.getLovCode(content); }
                                 */
                                String fieldName = importColumn.getField();
                                if ("id".equalsIgnoreCase(fieldName)) {
                                    continue;
                                }
                                Class<?> propertyType = null;
                                Field propertyFiled;
                                try {
                                    propertyFiled = clazz.getDeclaredField(fieldName);
                                    propertyType = propertyFiled.getType();
                                } catch (NoSuchFieldException e) {
                                    // TODO: handle exception
                                    LCLogger.info().withMessageKey("read2007Content")
                                            .withMessage("Read Field'" + fieldName + "'").flush();
                                    if (record instanceof BasicBaseModel) {
                                        LCLogger.info().withMessageKey("read2007Content")
                                                .withMessage("Read Ext Field '" + fieldName + "'").flush();
                                        if (!StringUtils.isBlank(content)) {
                                            if (((BasicBaseModel) record).getExtList() == null) {
                                                ((BasicBaseModel) record).setExtList(new ArrayList<BasicExtModel>());
                                            }
                                            BasicExtModel ext = new BasicExtModel();
                                            ext.setName(fieldName);
                                            ext.setValue(content);
                                            ((BasicBaseModel) record).getExtList().add(ext);
                                            ((BasicBaseModel) record).setExtFlag(true);
                                        }
                                        break;
                                    } else {
                                        LCLogger.withException(e);
                                    }
                                }
                                try {
                                    PropertyUtils.setProperty(record, fieldName,
                                            parseValueWithType(content, propertyType));
                                    // 值列表验证
                                    if (!StringUtils.isBlank(importColumn.getLov())) {
                                        String value = getKey(importColumn.getLovMap(), content);
                                        if (value == null) {
                                            errorinfo += "," + importColumn.getTitle() + "没有按照模板中选项选取";

                                        }
                                    }
                                    // 手机号、生日格式验证
                                    String contestTest = contestTest(fieldName, content);
                                    if (contestTest != null) {
                                        errorinfo += "," + contestTest;
                                    }

                                } catch (NoSuchMethodException e) {
                                    // TODO Auto-generated catch block
                                    LCLogger.withException(e);
                                } catch (IllegalAccessException e) {
                                    // TODO Auto-generated catch block
                                    LCLogger.withException(e);
                                } catch (InvocationTargetException e) {
                                    // TODO Auto-generated catch block
                                    LCLogger.withException(e);
                                }
                                break;
                            }
                        }
                    }
                    if (errorinfo != "") {
                        errorinfo = (String) errorinfo.subSequence(1, errorinfo.length());
                        setFiedValue(clazz, record, "accntValidate", "N");
                        setFiedValue(clazz, record, "validateInfo", errorinfo);
                    } else {
                        setFiedValue(clazz, record, "accntValidate", "Y");
                    }
                    setFiedValue(clazz, record, "accntSynchronize", "N");
                    result.add(record);
                }
            }
        } catch (Exception e) {
            // TODO: handle exception
            LCLogger.withException(e);
        }
        return result;
    }

    /**
     * <p>
     * 设置导入对象的默认值
     * </p>
     *
     * @author yrf
     * @param clazz
     * @param defaultMap
     * @param record
     */
    private <T> void setDefaultValue(Class<T> clazz, Map<?, ?> defaultMap, T record) {
        if (defaultMap == null || defaultMap.size() == 0) {
            return;
        }
        for (Entry<?, ?> entry : defaultMap.entrySet()) {
            try {
                Class<?> propertyType = null;
                Field propertyField;
                String field = entry.getKey().toString();
                String value = entry.getValue().toString();
                Field[] fields = clazz.getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {

                    if (fields[i].getName().equals(field)) {
                        propertyField = clazz.getDeclaredField(field);
                        propertyType = propertyField.getType();
                        PropertyUtils.setProperty(record, field, parseValueWithType(value, propertyType));
                    }
                }

            } catch (Exception e) {
                LCLogger.withException(e);
            }
        }
    }

    /**
     * 获取单元格的内容
     *
     * @param cell 单元格
     * @return 返回单元格内容
     */
    private String get2003CellContent(HSSFCell cell) {
        StringBuffer buffer = new StringBuffer();
        switch (cell.getCellType()) {
            // 数字
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    String timeStr = "h:mm";
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat(timeStr)) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    buffer.append(sdf.format(date));
                } else {
                    buffer.append(cell.getStringCellValue());
                }
                break;
            // 布尔
            case HSSFCell.CELL_TYPE_BOOLEAN:
                buffer.append(cell.getBooleanCellValue());
                break;
            // 公式
            case HSSFCell.CELL_TYPE_FORMULA:
                buffer.append(cell.getCellFormula());
                break;
            // 字符串
            case HSSFCell.CELL_TYPE_STRING:
                buffer.append(cell.getStringCellValue());
                break;
            // 空值
            case HSSFCell.CELL_TYPE_BLANK:
                // 故障
            case HSSFCell.CELL_TYPE_ERROR:
            default:
                break;
        }
        return buffer.toString();
    }

    /**
     * 获取单元格的内容
     *
     * @param cell 单元格
     * @return 返回单元格内容
     */
    private String get2007CellContent(XSSFCell cell) {
        StringBuffer buffer = new StringBuffer();
        try {
            switch (cell.getCellType()) {
                // 数字
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 处理日期格式、时间格式
                        SimpleDateFormat sdf = null;
                        String timeStr = "h:mm";
                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat(timeStr)) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        }
                        Date date = cell.getDateCellValue();
                        buffer.append(sdf.format(date));
                    } else {
                        DecimalFormat df = new DecimalFormat("#");
                        buffer.append(df.format(cell.getNumericCellValue()));
                    }
                    break;
                // 布尔
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    buffer.append(cell.getBooleanCellValue());
                    break;
                // 公式
                case XSSFCell.CELL_TYPE_FORMULA:
                    buffer.append(cell.getCellFormula());
                    break;
                // 字符串
                case XSSFCell.CELL_TYPE_STRING:
                    buffer.append(cell.getStringCellValue());
                    break;
                // 空值
                case XSSFCell.CELL_TYPE_BLANK:
                    // 故障
                case XSSFCell.CELL_TYPE_ERROR:
                default:
                    break;
            }
        } catch (Exception e) {
            // TODO: handle exception
        }

        return buffer.toString();
    }

    /**
     * 手机号验证
     */
    public String contestTest(String filed, String value) {
        String content = null;
        // 手机号验证
        String mobilephone1 = "mobilephone1";
        if (mobilephone1.equalsIgnoreCase(filed)) {
            String regex = "^((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(17[013678])|(18[0,5-9]))\\d{8}$";
            int mobileLen = 11;
            if (value.length() != mobileLen) {
                content = "手机号应为11位数";
            } else {
                Pattern p = Pattern.compile(regex);
                Matcher m = p.matcher(value);
                boolean isMatch = m.matches();
                if (!isMatch) {
                    content = "手机号格式错误";
                }
            }
        }
        String birthday = "BIRTHDAY";
        if (birthday.equalsIgnoreCase(filed)) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            try {
                // 设置lenient为false. 否则SimpleDateFormat会比较宽松地验证日期，比如2007/02/29会被接受，并转换成2007/03/01
                format.setLenient(false);
                format.parse(value);
            } catch (ParseException e) {
                // 如果throw java.text.ParseException或者NullPointerException，就说明格式不对
                content = "请按照2007-02-29格式";
            }
        }

        return content;
    }

    public <T> void setFiedValue(Class<T> clazz, T record, String fieldName, String content)
            throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        try {
            Field propertyFiled = clazz.getDeclaredField(fieldName);
            if (propertyFiled != null) {
                Class<?> propertyType = propertyFiled.getType();
                PropertyUtils.setProperty(record, fieldName, parseValueWithType(content, propertyType));
            }
        } catch (NoSuchFieldException e) {
            // TODO Auto-generated catch block
            LCLogger.withException(e);
        } catch (SecurityException e) {
            // TODO Auto-generated catch block
            LCLogger.withException(e);
        }
    }

    /**
     * 判断单元格里的值是否在值列表中
      */
    public String getKey(Map<String, String> map, String value) {
        String key = null;
        for (Map.Entry<String, String> entry : map.entrySet()) {
            if (value.equals(entry.getValue())) {
                key = entry.getKey();
            }
        }
        return key;
    }
}
